USE master;
GO

SET NOCOUNT ON;
GO

PRINT 'single use plans';
SELECT
	objtype AS cache_type,
    COUNT_BIG(*) AS total_plan_count,
	CAST(SUM(size_in_bytes/POWER(1024.0, 2)) AS decimal(18,2)) AS total_plan_mb,
    AVG(usecounts) AS avg_use_count,
	CAST(SUM(
		CASE WHEN usecounts = 1
		THEN size_in_bytes ELSE 0 END
	)/POWER(1024.0, 2) AS decimal(18,2)) AS total_plan_mb_use_count_1,
	SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS total_plan_use_count_1
FROM sys.dm_exec_cached_plans WITH (NOLOCK)
GROUP BY objtype
ORDER BY total_plan_mb_use_count_1 DESC OPTION (RECOMPILE);

PRINT 'single-use, ad-hoc and prepared queries that are bloating the plan cache';
SELECT TOP(50)
	DB_NAME(st.dbid) AS database_name,
	st.text AS query_text, 
	cp.objtype AS object_type,
	cp.cacheobjtype AS cache_object_type,  
	cp.size_in_bytes/1024 AS plan_size_kb
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
WHERE cp.cacheobjtype = N'Compiled Plan' 
AND cp.objtype IN (N'Adhoc', N'Prepared') 
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC, DB_NAME(st.[dbid]) ASC OPTION (RECOMPILE);

USE <database_name>;
GO

-- how many plans does EACH query_hash have?
SELECT
	qs.query_hash,
	COUNT(qs.query_hash) AS plan_count,
    COUNT(DISTINCT qs.query_plan_hash) AS distinct_plan_count,
    SUM(qs.execution_count) AS total_execution_count
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
GROUP BY qs.query_hash
ORDER BY total_execution_count DESC OPTION (RECOMPILE);

/*
When the "distinct_plan_count" is mostly 1 for your queries
then you MIGHT consider using forced parameterization.

However, before you turn this on - you might want to get
more details about the queries that have MULTIPLE plans.
Get the hash of the queries with multiple plans and the highest execution count
and paste it into the following T-SQL in the WHERE clause:
*/
SELECT
	q.plan_count,
    q.distinct_plan_count,
    st.text AS query_text,
    qp.query_plan AS query_plan
FROM (SELECT query_hash,
		COUNT(DISTINCT(query_hash)) AS distinct_plan_count,
        COUNT(query_hash) AS plan_count
      FROM sys.dm_exec_query_stats WITH (NOLOCK)
      GROUP BY query_hash
      ) AS q
      INNER JOIN sys.dm_exec_query_stats qs WITH (NOLOCK) ON q.query_hash = qs.query_hash
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE plan_count > 1
AND qs.query_hash = <query_hash>
ORDER BY q.plan_count DESC OPTION (RECOMPILE);

-- get the query plan and sql text from the query_hash
SELECT
	qs.query_hash,
	qs.query_plan_hash,
	qp.query_plan,
	st.text AS query_text
FROM sys.dm_exec_query_stats as qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.query_hash = <query_hash> OPTION (RECOMPILE);

-- review a sampling of the queries (grouping by the query_hash)
-- and see which have the highest *avg* CPU time
SELECT
	qs2.query_hash,
	qs2.query_plan_hash,
	avg_cpu_time_ms
		= CAST((SUM(qs2.total_worker_time) /
			SUM(qs2.execution_count) / 1000.0) AS DECIMAL(12,2)),
	example_statement_text
		= MIN(qs2.statement_text)
 FROM (SELECT qs.*,  
        statement_text = SUBSTRING(st.text, 
			(qs.statement_start_offset / 2) + 1
	    	, ((CASE statement_end_offset 
				WHEN - 1 THEN DATALENGTH(st.text) 
				ELSE qs.statement_end_offset 
				END 
		        - qs.statement_start_offset) / 2) + 1) 
		FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) 
			CROSS APPLY sys.dm_exec_sql_text
				 (qs.sql_handle) AS st) AS qs2
GROUP BY qs2.query_hash, qs2.query_plan_hash
ORDER BY avg_cpu_time_ms DESC OPTION (RECOMPILE);

-- review a sampling of the queries (grouping by the query_hash)
-- and see which have the highest cumulative effect by CPU time
SELECT
	qs2.query_hash,
	CAST(SUM(qs2.total_worker_time) / 1000.0 AS DECIMAL(12,2))
		AS total_cumulative_cpu_time_ms,
	COUNT(DISTINCT qs2.query_plan_hash)
		AS number_of_plans,
	SUM(qs2.execution_count) AS number_of_executions,
	MIN(qs2.statement_text) AS example_statement_text
 FROM (SELECT qs.*,  
        statement_text = SUBSTRING(st.text, 
			(qs.statement_start_offset / 2) + 1,
	    	((CASE statement_end_offset 
				WHEN - 1 THEN DATALENGTH(st.text) 
				ELSE qs.statement_end_offset
				END 
		        - qs.statement_start_offset) / 2) + 1) 
		FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
			CROSS APPLY sys.dm_exec_sql_text
				 (qs.sql_handle) AS st) AS qs2
GROUP BY qs2.query_hash 
ORDER BY total_cumulative_cpu_time_ms DESC OPTION (RECOMPILE);

/*
An easy way to detect applications that submit lots of ad hoc queries
is by grouping on the sys.dm_exec_query_stats.query_hash column.
Queries that have a number_of_entries value in the hundreds or thousands
are excellent candidates for parameterization.
*/
SELECT
	q.query_hash, 
	q.number_of_entries, 
	t.text AS sample_query, 
	p.query_plan AS sample_plan
FROM (SELECT TOP 20 query_hash, 
			COUNT(*) AS number_of_entries, 
			MIN(sql_handle) AS sample_sql_handle, 
			MIN(plan_handle) AS sample_plan_handle
		FROM sys.dm_exec_query_stats WITH (NOLOCK)
		GROUP BY query_hash
		HAVING COUNT(*) > 1
		ORDER BY COUNT(*) DESC) AS q
CROSS APPLY sys.dm_exec_sql_text(q.sample_sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(q.sample_plan_handle) AS p OPTION (RECOMPILE);

-- setting the PARAMETERIZATION option to FORCED
-- ALTER DATABASE <database_name> SET PARAMETERIZATION FORCED;